CREATE PROC [dbo].[EPMPreCalculateSupplementaryInsurance]
    @PrescriptionType TINYINT,
    @PrescriptionId UNIQUEIDENTIFIER,
    @PyramidPrescriptionTempId INT = NULL,
    @IncurableTransactionId SMALLINT = 0
AS
BEGIN
    /* ----------------- Last Update 03-08-07 ----------------- */
    BEGIN TRY
        /*--------------------------------------------------------------------------------------- Get IHIO Prescription Detail*/
        IF @PrescriptionType = 1
        BEGIN
            WITH IHIO
            AS (SELECT D.Id,
                       D.RowIndex,
                       D.GoodsCode,
                       D.IRCCode,
                       ISNULL(D.CoveredCount, 0) CoveredCount,
                       CASE
                           WHEN ISNULL(D.CoveredCount, 0) > 0 THEN
                               D.CoveredCount
                           ELSE
                               D.DeliverCount
                       END DeliverCount,
                       CASE
                           WHEN D.InsurerAmount < D.BasePrice
                                AND D.ExchangeAmount <> D.InsurerAmount
                                AND D.CoveredCount >= D.DeliverCount
                                AND D.InsurerPercent < 100 THEN
                               D.InsurerAmount
                           WHEN D.CoveredCount = D.DeliverCount
                                AND D.InsurerPercent = 100
                                AND D.BasePrice > D.InsurerAmount THEN
                               D.InsurerAmount
                           ELSE
                               D.BasePrice
                       END SalesPriceAmount,
                       ISNULL(D.CoveredCount, 0) * CASE
                                                       WHEN D.InsurerAmount < D.BasePrice
                                                            AND D.ExchangeAmount <> D.InsurerAmount
                                                            AND D.CoveredCount >= D.DeliverCount
                                                            AND D.InsurerPercent < 100 THEN
                                                           D.InsurerAmount
                                                       WHEN D.CoveredCount = D.DeliverCount
                                                            AND D.InsurerPercent = 100
                                                            AND D.BasePrice > D.InsurerAmount THEN
                                                           D.InsurerAmount
                                                       ELSE
                                                           D.BasePrice
                                                   END TotalPrice,
                       CASE
                           WHEN ISNULL(D.CoveredCount, 0) = 0 THEN
                               0
                           WHEN D.InsurerAmount = D.ExchangeAmount
                                AND D.InsurerPercent = 0
                                AND ISNULL(D.CoveredCount, 0) >= D.DeliverCount THEN
                               ROUND(
                                        100 - D.TotalInsurerAmount * 100
                                        / CAST((D.TotalInsuredAmount + D.TotalInsurerAmount) AS FLOAT),
                                        6
                                    )
                           WHEN D.TotalInsurerAmount > 0
                                AND
                                (
                                    D.DeliverCount * D.InsurerAmount > 0
                                    OR D.CoveredCount * D.BasePrice > 0
                                )
                                AND D.BasePrice >= D.InsurerAmount THEN
                               ROUND(
                                        100 - D.TotalInsurerAmount * 100
                                        / CAST(D.CoveredCount * D.InsurerAmount AS FLOAT),
                                        6
                                    )
                           WHEN NOT (
                                        D.TotalInsurerAmount > 0
                                        AND
                                        (
                                            D.DeliverCount * D.InsurerAmount > 0
                                            OR D.CoveredCount * D.BasePrice > 0
                                        )
                                    ) THEN
                               100
                           ELSE
                               ROUND(100 - D.TotalInsurerAmount * 100 / CAST(D.CoveredCount * D.BasePrice AS FLOAT), 6)
                       END InsuredFranshiz,
                       D.BasePrice,
                       ISNULL(D.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                       D.HasDuty
                FROM dbo.IHIOPrescriptionDetail D
                WHERE PrescriptionId = @PrescriptionId
                      AND DeliverCount > 0
                      AND ISNULL(D.CoveredCount, 0) > 0
                UNION ALL
                SELECT D.Id,
                       D.RowIndex,
                       D.GoodsCode,
                       D.IRCCode,
                       0 CoveredCount,
                       D.DeliverCount - ISNULL(D.CoveredCount, 0) DeliverCount,
                       D.BasePrice SalesPriceAmount,
                       (D.DeliverCount - ISNULL(D.CoveredCount, 0)) * D.BasePrice TotalPrice,
                       100 InsuredFranshiz,
                       0 BasePrice,
                       ISNULL(D.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                       D.HasDuty
                FROM dbo.IHIOPrescriptionDetail D
                WHERE PrescriptionId = @PrescriptionId
                      AND D.DeliverCount > 0
                      AND D.DeliverCount > ISNULL(D.CoveredCount, 0))
            /*INSERT INTO @PrescriptionDetail
            (
                Id,
                RowIndex,
                GoodsCode,
                GenericCode,
                IRCCode,
                InsuredFranshiz,
                StatusCode,
                CoverCount,
                DeliverCount,
                SalesPriceAmount,
                IncurablePaidAmount,
                InsuredAmount,
                NonInsuranceAmount,
                EquipmentAmount,
                GoodsAmount,
                DifferenceAmount,
                InsuredPaidAmount,
                TotalPrice
            )*/
            SELECT IHIO.Id,
                   IHIO.RowIndex,
                   IHIO.GoodsCode,
                   '' GenericCode,
                   IHIO.IRCCode,
                   CASE
                       WHEN IHIO.InsuredFranshiz = 100 THEN
                           0
                       ELSE
                           IHIO.InsuredFranshiz
                   END InsuredFranshiz,
                   CASE
                       WHEN IHIO.InsuredFranshiz = 100 THEN
                           0
                       WHEN IHIO.InsuredFranshiz = 0 THEN
                           4
                       WHEN IHIO.InsuredFranshiz < 100
                            AND IHIO.CoveredCount * (IHIO.BasePrice - SalesPriceAmount) > 0 THEN
                           6
                       WHEN IHIO.CoveredCount * (IHIO.BasePrice - SalesPriceAmount) = 0 THEN
                           5
                       ELSE
                           0
                   END StatusCode,
                   IHIO.CoveredCount,
                   IHIO.DeliverCount,
                   IHIO.SalesPriceAmount,
                   0 IncurablePaidAmount,
                   ROUND(   IHIO.TotalPrice * .01 * CASE
                                                        WHEN IHIO.InsuredFranshiz = 100 THEN
                                                            0
                                                        ELSE
                                                            IHIO.InsuredFranshiz
                                                    END,
                            0
                        ) InsuredAmount,
                   0 NonInsuranceAmount,
                   0 EquipmentAmount,
                   0 GoodsAmount,
                   IHIO.CoveredCount * (IHIO.BasePrice - SalesPriceAmount) DifferenceAmount,
                   ROUND(IHIO.TotalPrice * IHIO.InsuredFranshiz * .01, 0)
                   + (IHIO.CoveredCount * (IHIO.BasePrice - SalesPriceAmount)) InsuredPaidAmount,
                   IHIO.TotalPrice,
                   ISNULL(IHIO.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                   IHIO.HasDuty
            FROM IHIO
            ORDER BY IHIO.RowIndex;
        END;
        /*--------------------------------------------------------------------------------------- Get TAMIN Prescription Detail*/
        IF @PrescriptionType = 0
        BEGIN
            /*INSERT INTO @PrescriptionDetail
            (
                Id,
                RowIndex,
                GoodsCode,
                GenericCode,
                IRCCode,
                InsuredFranshiz,
                StatusCode,
                CoverCount,
                DeliverCount,
                SalesPriceAmount,
                IncurablePaidAmount,
                InsuredAmount,
                NonInsuranceAmount,
                EquipmentAmount,
                GoodsAmount,
                DifferenceAmount,
                InsuredPaidAmount,
                TotalPrice
            )*/
            SELECT D.Id,
                   D.RowIndex,
                   D.GoodsCode,
                   D.GenericCode,
                   '' IRCCode,
                   CASE
                       WHEN (D.Franshiz = 0)
                            OR
                            (
                                D.Franshiz > 0
                                AND D.InsurerCeilingAmount = 0
                            ) THEN
                           0
                       ELSE
                           ROUND(100 - D.InsurerAmount * 100 / CAST((D.InsurerAmount + D.InsuredAmount) AS FLOAT), 6)
                   END AS InsuredFranshiz,
                   CASE
                       WHEN D.Franshiz = 100 THEN
                           4
                       WHEN (D.Franshiz = 0)
                            OR
                            (
                                D.Franshiz > 0
                                AND D.InsurerCeilingAmount = 0
                            ) THEN
                           0
                       WHEN D.DifferenceAmount > 0 THEN
                           6
                       WHEN D.DifferenceAmount = 0
                            AND Franshiz > 0 THEN
                           5
                       ELSE
                           0
                   END AS StatusCode,
                   D.MaxCoveredCount CoverCount,
                   D.DeliverCount,
                   (D.InsurerAmount + ROUND(D.InsuredAmount, 0)) / D.DeliverCount SalesPriceAmount,
                   CASE
                       WHEN D.InsuredAmount < D.SupportAmount THEN
                           D.InsuredAmount
                       ELSE
                           D.SupportAmount
                   END IncurablePaidAmount,
                   CASE
                       WHEN (D.Franshiz = 0)
                            OR
                            (
                                D.Franshiz > 0
                                AND D.InsurerCeilingAmount = 0
                            ) THEN
                           0
                       ELSE
                           D.InsuredAmount
                   END InsuredAmount,
                   0 NonInsuranceAmount,
                   0 EquipmentAmount,
                   0 GoodsAmount,
                   D.DifferenceAmount,
                   D.InsuredAmount + D.DifferenceAmount InsuredPaidAmount,
                   (D.InsurerAmount + ROUND(D.InsuredAmount, 0)) TotalPrice,
                   ISNULL(D.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                   D.HasDuty
            FROM dbo.TaminPrescriptionDetail D
            WHERE D.PrescriptionId = @PrescriptionId
                  AND D.DeliverCount > 0;
        END;
        /*--------------------------------------------------------------------------------------- Get TTAC Prescription Detail*/
        IF @PrescriptionType = 3
        BEGIN
            WITH TTACDetail
            AS (SELECT D.Id,
                       D.GoodsCode,
                       D.GenericCode,
                       ISNULL(D.CoverCount, 0) CoverCount,
                       CASE
                           WHEN D.InsurerAmount > 0
                                AND D.DeliverCount > D.CoverCount THEN
                               D.CoverCount
                           ELSE
                               D.DeliverCount
                       END DeliverCount,
                       CASE
                           WHEN D.DifferenceAmount > 0
                                AND D.DeliverCount = D.CoverCount THEN
                               ROUND((D.TotalAmount - D.DifferenceAmount) / D.DeliverCount, 6)
                           WHEN D.DifferenceAmount > 0
                                AND D.DeliverCount <> D.CoverCount THEN
                               ROUND(
                                        (D.TotalAmount - ((D.DeliverCount - D.CoverCount) * D.SalesPriceAmount)
                                         - D.DifferenceAmount
                                        ) / D.CoverCount,
                                        6
                                    )
                           ELSE
                               D.SalesPriceAmount
                       END SalesPriceAmount,
                       ISNULL(
                                 ROUND(
                                          D.CoverCount
                                          * CASE
                                                WHEN D.DifferenceAmount > 0
                                                     AND D.DeliverCount = D.CoverCount THEN
                                                    ROUND((D.TotalAmount - D.DifferenceAmount) / D.DeliverCount, 6)
                                                WHEN D.DifferenceAmount > 0
                                                     AND D.DeliverCount <> D.CoverCount THEN
                                                    ROUND(
                                                             (D.TotalAmount
                                                              - ((D.DeliverCount - D.CoverCount) * D.SalesPriceAmount)
                                                              - D.DifferenceAmount
                                                             ) / D.CoverCount,
                                                             6
                                                         )
                                                ELSE
                                                    D.SalesPriceAmount
                                            END * (100 - ROUND(D.Franshiz, 6)) * .01,
                                          0
                                      ),
                                 0
                             ) InsuredAmount,
                       CASE
                           WHEN D.DifferenceAmount > 0
                                AND D.DeliverCount = D.CoverCount THEN
                               ROUND((D.TotalAmount - D.DifferenceAmount), 6)
                           WHEN D.DifferenceAmount > 0
                                AND D.DeliverCount <> D.CoverCount THEN
                       (D.TotalAmount - ((D.DeliverCount - D.CoverCount) * D.SalesPriceAmount) - D.DifferenceAmount)
                           WHEN ISNULL(D.CoverCount, 0) = 0 THEN
                               D.TotalAmount
                           ELSE
                               D.SalesPriceAmount * D.CoverCount
                       END TotalPrice,
                       D.DifferenceAmount,
                       D.TotalAmount - D.InsurerAmount InsuredPaidAmount,
                       CASE
                           WHEN D.Franshiz = 0 THEN
                               0
                           ELSE
                               100 - D.Franshiz
                       END InsuredFranshiz,
                       D.StatusCode,
                       D.RowIndex,
                       ISNULL(D.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                       D.HasDuty
                FROM dbo.TTACPrescriptionDetail D
                WHERE D.PrescriptionId = @PrescriptionId
                UNION ALL
                SELECT D.Id,
                       D.GoodsCode,
                       D.GenericCode,
                       0 CoverCount,
                       D.DeliverCount - D.CoverCount DeliverCount,
                       D.SalesPriceAmount,
                       0 InsuredAmount,
                       (D.DeliverCount - D.CoverCount) * D.SalesPriceAmount TotalPrice,
                       0 DifferenceAmount,
                       (D.DeliverCount - D.CoverCount) * D.SalesPriceAmount InsuredPaidAmount,
                       0 InsuredFranshiz,
                       0 StatusCode,
                       D.RowIndex,
                       ISNULL(D.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                       D.HasDuty
                FROM dbo.TTACPrescriptionDetail D
                WHERE D.PrescriptionId = @PrescriptionId
                      AND D.InsurerAmount > 0
                      AND D.DeliverCount > D.CoverCount)
            /*INSERT INTO @PrescriptionDetail
            (
                Id,
                RowIndex,
                GoodsCode,
                GenericCode,
                IRCCode,
                InsuredFranshiz,
                StatusCode,
                CoverCount,
                DeliverCount,
                SalesPriceAmount,
                IncurablePaidAmount,
                InsuredAmount,
                NonInsuranceAmount,
                EquipmentAmount,
                GoodsAmount,
                DifferenceAmount,
                InsuredPaidAmount,
                TotalPrice
            )*/
            SELECT TTACDetail.Id,
                   TTACDetail.RowIndex,
                   TTACDetail.GoodsCode,
                   TTACDetail.GenericCode,
                   '' IRCCode,
                   ROUND(TTACDetail.InsuredFranshiz, 6) InsuredFranshiz,
                   TTACDetail.StatusCode,
                   TTACDetail.CoverCount,
                   ROUND(TTACDetail.DeliverCount, 3) DeliverCount,
                   TTACDetail.SalesPriceAmount,
                   0 IncurablePaidAmount,
                   TTACDetail.InsuredAmount,
                   0 NonInsuranceAmount,
                   0 EquipmentAmount,
                   0 GoodsAmount,
                   TTACDetail.DifferenceAmount,
                   TTACDetail.InsuredPaidAmount,
                   ROUND(TTACDetail.TotalPrice, 0) TotalPrice,
                   ISNULL(TTACDetail.IsIncludeSupplementaryInsurance, 1) IsIncludeSupplementaryInsurance,
                   TTACDetail.HasDuty
            FROM TTACDetail
            ORDER BY TTACDetail.RowIndex;
        END;
        /*--------------------------------------------------------------------------------------- Get Pyramid Temp Prescription Detail*/
        IF @PrescriptionType = 4
        BEGIN
            DELETE FROM dbo.Tmp_Over_Under_Row_Drug
            WHERE PrescriptionId IN
                  (
                      SELECT AutoId
                      FROM dbo.TmpDrugHavaleh
                      WHERE Id_Havaleh = @PyramidPrescriptionTempId
                  )
                  AND Code_Act = 10
                  AND Kind_Act = 10
                  AND Code_Over_Under <> ISNULL(@IncurableTransactionId, 0);


            SELECT NEWID() Id,
                   D.Radif RowIndex,
                   D.K_Code GoodsCode,
                   '' GenericCode,
                   '' IRCCode,
                   ROUND(   CASE
                                WHEN D.BimarPercent = 100 THEN
                                    0
                                ELSE
                                    D.BimarPercent
                            END,
                            6
                        ) InsuredFranshiz,
                   D.Status StatusCode,
                   CASE
                       WHEN D.Status = 0 THEN
                           0
                       ELSE
                           D.K_Qty1
                   END CoverCount,
                   D.K_Qty1 DeliverCount,
                   D.Price_Forosh SalesPriceAmount,

                   --CASE
                   --     WHEN ISNULL(TR.Price, 0) = 0 THEN
                   --         0
                   --     ELSE
                   --         CASE
                   --             WHEN D.Status IN ( 0, 4 )
                   --                  OR D.BimarPercent = 0 THEN
                   --                 0
                   --             WHEN D.BimarPercent <> 0
                   --                  AND (D.Tot_forosh * D.BimarPercent * .01) < ISNULL(TR.Price, 0) THEN
                   -- (D.Tot_forosh * D.BimarPercent * .01)
                   --             WHEN D.BimarPercent <> 0
                   --                  AND (D.Tot_forosh * D.BimarPercent * .01) >= ISNULL(TR.Price, 0) THEN
                   --                 ISNULL(TR.Price, 0)
                   --             ELSE
                   --                 CASE
                   --                     WHEN D.Tot_forosh < ISNULL(TR.Price, 0) THEN
                   --                         D.Tot_forosh
                   --                     ELSE
                   --                         ISNULL(TR.Price, 0)
                   --                 END
                   --         END
                   -- END IncurablePaidAmount,

                   ISNULL(TR.Price, 0) IncurablePaidAmount,
                   ROUND(   CASE
                                WHEN D.Status IN ( 0, 4 )
                                     OR D.BimarPercent = 0 THEN
                                    0
                                WHEN D.BimarPercent <> 0 THEN
                                    D.Tot_forosh * D.BimarPercent * .01
                                ELSE
                                    D.Tot_forosh
                            END,
                            4
                        ) InsuredAmount,
                   CAST(D.AutoId AS INT) GoodsAmount,
                   0 EquipmentAmount,
                   0 GoodsAmount,
                   D.Tot_Differ DifferenceAmount,
                   ROUND(   CASE
                                WHEN D.Status = 4 THEN
                                    D.Tot_Differ
                                WHEN D.BimarPercent <> 0 THEN
                                    D.Tot_forosh * D.BimarPercent * .01 + D.Tot_Differ
                                ELSE
                                    D.Tot_forosh + D.Tot_Differ
                            END,
                            0
                        ) InsuredPaidAmount,
                   D.Tot_forosh TotalPrice,
                   ISNULL(D.Serial_Flag, 1) IsIncludeSupplementaryInsurance,
                   CAST(0 AS BIT) AS HasDuty
            FROM dbo.TmpDrugHavaleh D
                LEFT JOIN dbo.Tmp_Over_Under_Row_Drug TR
                    ON TR.PrescriptionId = D.AutoId
                       AND TR.Code_Over_Under = @IncurableTransactionId
                       AND TR.Price > 0
            WHERE D.[Status] <> 8
                  AND D.Id_Havaleh = @PyramidPrescriptionTempId;
        END;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
        RAISERROR(
                     '         ʘ        [ 100- ]  ',
                     16,
                     1
                 );
        DECLARE @JsonErrorBody VARCHAR(MAX);
        SELECT @JsonErrorBody
            = '['
              + STUFF(
        (
            SELECT ',{' + '"PrescriptionType":' + CAST(@PrescriptionType AS VARCHAR(3)) + ','
                   + COALESCE('"PrescriptionId":"' + CAST(@PrescriptionId AS VARCHAR(100)) + '",', '')
                   + COALESCE(
                                 '"PyramidPrescriptionTempId":'
                                 + CAST(ISNULL(@PyramidPrescriptionTempId, 0) AS VARCHAR(10)),
                                 ''
                             ) + '}'
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(MAX)'),
        1   ,
        1   ,
        ''
                     ) + ']';

        INSERT INTO dbo.IHIOActivityHistory
        (
            ActionCode,
            EntityName,
            JsonActivity,
            JsonResult,
            CreatedBy
        )
        VALUES
        (3, 'PreCalculateSupplementaryInsurance', @JsonErrorBody, @ErrorMessage, 100);

        PRINT ERROR_MESSAGE();
    END CATCH;
END;
